Real MySQL 8.0 (4)

ordilov / 2022. 3. 4

인덱스는 데이터베이스 쿼리 성능에 중요하며 각 인덱스마다 특성과 차이가 있습니다.

먼저 디스크 읽기 방식에는 랜덤 I/O와 순차 I/O가 존재합니다. 데이터 저장 매체는 컴퓨터에서 가장 느린 부분으로 데이터베이스 성능 튜닝도 디스크 I/O를 줄이는 것이 중요합니다.

CPU나 메모리는 전자식 장치지만 하드 디스크 드라이브는 기계식 장치로 병목 지점이 됩니다. 이 때문에 전자식 저장 매체인 SSD를 많이 사용합니다.

순차 I/O에서는 SSD가 하드 디스크 드라이브와 비슷한 속도를 내지만 랜덤 I/O에서는 훨씬 빠릅니다. 데이터베이스에서는 순차적으로 읽기보다 작은 데이터를 랜덤으로 읽고 쓰는 일이 많아 SSD가 유리합니다.

랜덤 I/O 라는 표현은 하드 디스크 드라이브의 플래터를 돌려서 데이터가 저장된 위치를 디스크 헤더로 이동시켜 읽는 걸 의미합니다. 그 다음 데이터를 읽는 것은 순차 I/O나 랜덤 I/O나 동일합니다. 차이점은 순차로 읽는 경우 한 번의 시스템 콜이면 연속적으로 읽을 수 있지만 랜덤은 개별로 다시 요청해야 합니다. 그만큼 디스크 헤더를 옮기면서 시간이 오래 걸리게 됩니다. 즉 디스크의 성능은 디스크 헤더의 위치 이동 없이 얼마나 많은 데이터를 기록하느냐에 결정됩니다. 디스크 원판이 없는 SSD에서도 랜덤 I/O는 전체 스루풋이 떨어집니다.

쿼리를 튜닝해서 랜덤 I/O를 순차 I/O로 바꿔서 실행할 방법은 많지 않지만 목적은 랜덤 I/O 자체를 줄여주는 것입니다. 꼭 필요한 데이터만 읽도록 쿼리를 개선하는 작업입니다.

인덱스는 말그대로 목차에 해당하며 원하는 페이지의 주소를 빨리 찾기 위해 부분마다 찾아갈 수 있는 정보를 정렬해서 보관합니다. 다른 특징으로 데이터는 ArrayList처럼 정렬 없이 보관되어 있지만 인덱스는 SortedList처럼 항상 정렬된 상태를 유지합니다.

SortedList처럼 인덱스도 장단점이 존재하는데 값이 저장될 때마다 항상 정렬이 필요해 느리지만, 값을 찾을 때는 빠릅니다. 데이터의 저장 성능을 희생해 읽기 속도를 높이는 기능입니다.

프라이머리 키는 레코드를 대표하는 칼럼의 값으로 만들어진 인덱스를 의미합니다. 중복이나 NULL을 허용하지 않습니다. 프라이머리 키를 제외한 인덱스는 모두 세컨더리 인덱스로 분류합니다.

인덱스 저장 방식은 대표적으로 B-Tree와 Hash 인덱스로 구분합니다. 둘의 차이점은 B-Tree는 칼럼 값을 변형하지 않고 인덱싱하고 Hash는 값을 변형해서 인덱싱합니다. 완전 일치 값일 때는 Hash가 빠르지만 값을 변형해서 사용해 값의 일부 일치나 범위 검색에 사용할 수 없습니다.

데이터의 중복 허용 여부로 분류하면 유니크 인덱스와 유니크하지 않은 인덱스로 구분합니다. 이는 옵타마이저에게 상당히 중요한게 유니크 인덱스를 동등 조건으로 비교하면 1건만 찾으면 검색을 종료해도 됨을 알려줍니다.

가장 먼저 도입되었고 일반적이며 범용적인 목적으로 사용되는 인덱스 알고리즘입니다. 일반적으로 B-Tree의 변형된 형태인 B+-Tree 또는 B*-Tree로 사용됩니다. B-Tree의 B는 Balanced를 의미합니다.

B-Tree는 트리 구조는 다음과 같이 구성됩니다.

• 최상위에 하나의 루트 노드가 존재하고 하위에 자식 노드가 붙어있습니다.
• 가장 하위에 있는 노드를 리프 노드
• 트리 구조에서 루트 노드도 아니고 리프 노드도 아닌 중간 노드를 브랜치 노드라고 합니다.

인덱스는 실제 데이터가 저장된 데이터는 따로 관리됩니다. 리프 노드가 실제 데이터 레코드를 찾아 가기 위한 주솟값을 가집니다. 인덱스의 키 값은 모두 정렬되어 있지만 데이터 파일의 레코드는 정렬되어 있지 않고 임의의 순서로 저장되어 있습니다. 레코드가 INSERT한 순서대로 저장되어 있지 않고 가능한 공간에 넣도록 설계되어 있어 순서는 달라질 수 있습니다.

B-Tree에 새로운 값이 저장될 때 저장될 위치를 검색하고 리프 노드에 레코드의 키 값과 주소 정보를 저장합니다. 리프 노드가 꽉차서 더 자장할 수 없는 경우 리프 노드를 분리해야 하는데, 이 작업에서 상위 브랜치 노드까지 처리해야 합니다. 이런 작업 탓에 B-Tree는 상대적으로 쓰기 작업에 비용이 많이 듭니다. 이로 인한 영향의 크기를 대략적으로 계산하면 기본 비용이 1일 때 인덱스 추가 작업은 1.5 정도로 계산합니다.

키 값이 삭제되는 경우는 해당 키의 리프 노드를 찾아서 삭제 마크만 하면 됩니다. 삭제 마킹된 공간은 그대로 방치해서 재활용할 수 있습니다. 마킹 작업조차도 디스크 쓰기가 필요하기 때문에 InnoDB에서는 버퍼링되어 지연 처리될 수 있습니다. MyISAM이나 MEMORY 엔진의 경우 버퍼 기능이 없으므로 인덱스 키가 바로 삭제됩니다.

인덱스의 키 값은 리프 노드의 위치와 연관되어 있기 때문에 단순 인덱스 키 값 변경은 불가능합니다. 변경을 위해선 키 값을 삭제하고 다시 새로운 키값을 추가하는 형태로 처리됩니다. 키 값의 변경 때문에 발생하는 B-Tree 인덱스 키 값의 삭제와 추가 작업은 절차대로 처리됩니다.

인덱스 검색은 루트 노드부터 시작해서 최종 리프 노드까지 이동하며 비교 작업을 수행하며 이를 트리 검색이라고 합니다. 트리 검색은 SELECT 뿐만 아니라 UPDATE, DELETE 시에도 해당 레코드를 먼저 검색해야 할 때도 사용됩니다. B-Tree 인덱스 검색은 완전 일치나 앞 부분 일치, 비교 조건에도 활용할 수 있습니다. 하지만 앞 부분을 잘라서 키 값으로 사용하기 때문에 뒷부분만 검색하는 용도로는 사용할 수 없습니다. 또 함수나 연산 수행 결과로 인덱스 키 값에 변형된 경우 B-Tree 검색 기능을 사용할 수 없습니다.

인덱스를 구성하는 칼럼의 크기, 레코드 건수, 유니크한 인덱스 키 값의 개수등에 따라 영향을 받습니다.

InnoDB 엔진은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지 또는 블록이라고 하며 읽기, 쓰기 작업의 최소 작업 단위입니다. 페이지는 버퍼 풀에서 데이터를 버퍼링하는 기본 단위이기도 합니다. 인덱스도 결국은 페이지 단위로 관리되며 루트와 브랜치, 리프 노드도 페이지 단위로 구분됩니다.

이진 트리는 각 노드가 자식 노드 2개만 가지는데 B-Tree가 이진 트리라면 인덱스 검색이 상당히 비효율적입니다. 따라서 B-Tree의 자식 노드의 개수는 가변적으로 페이지 크기와 키 값의 크기에 따라 결정됩니다. 인덱스의 페이지 크기의 기본값은 16KB로 4KB ~ 64KB 사이의 값으로 선택할 수 있습니다. 인덱스의 키 값의 크기를 16바이트라고 하면 키 값과 함께 6 ~ 12바이트 크기의 자식 노드 주소를 담은 데이터가 위치합니다.

여기서 저장할 수 있는 키의 개수는 페이지 크기 / (키 값의 크기 + 자식 노드 주소 크기) 가 됩니다. 인덱스 키 값이 커지면 그만큼 저장할 수 있는 키의 개수가 줄어들게 됩니다. 레코드를 찾을 때 이 개수를 넘는 만큼 디스크로부터 읽어야 하는 횟수가 늘어나고 그만큼 으려지게 됩니다. 또 키 값의 크기가 늘어나면 인덱스를 캐시해두는 영역에 보관할 수 있는 양이 줄어들어 메모리 효율이 줄어듭니다.

B-Tree의 깊이는 중요하지만 직접 제어할 방법은 없습니다. 인덱스 키 값이 커질수록 페이지에 담을 수 개수가 적어져 깊이가 깊어집니다. 따라서 중요한 것은 인덱스 키 값은 가능하면 작게 만드는 것이 좋습니다.

선택도 또는 기수성은 거의 같은 의미로 사용되며 인덱스 키 값 가운데 유니크한 값의 수를 의미합니다. 인덱스 키 값이 100개일 때 유니크한 값의 수가 10개라면 기수성은 10입니다. 인덱스 키 값 가운데 중복된 값이 많아질 수록 기수성은 낮아지고 선택도가 떨어집니다. 인덱스는 선택도가 높을수록 검색 대상이 줄어들기 때문에 그만큼 빠르게 처리됩니다.

예를 들어 레코드 건수가 1만 건이고 한 칼럼으로 인덱스가 생성된 두 케이스를 비교하겠습니다.

• 케이스 A: 칼럼의 유니크한 값의 개수가 10개
• 케이스 B: 칼럼의 유니크한 값의 개수가 1000개

Where으로 해당 칼럼을 비교하는 경우 A는 1000개씩 비교하고 B는 10개씩 비교하게 됩니다. 만약 해당 케이스의 데이터가 1개라고 한다면 비교할 때 A는 1을 위해 999개를 더 읽게 됩니다. B는 1을 위해 9건만 더 읽을 수 있기 때문에 효율적입니다.

인덱스를 통해 테이블의 레코드를 읽는 것은 인덱스를 거치지 않고 바로 레코드를 읽는 것보다 비용이 높습니다. 예를 들어 100만 개의 레코드에서 50만 건을 읽을 때 전부 읽고 50만 건을 버릴지, 인덱스로 50만 건만 읽어올지 판단해야 합니다. 옵티마이저에서 인덱스를 통해 1건을 읽는 게 직접 1건 읽는 것보다 4~5배 비용이 더 든다고 예측합니다. 인덱스를 통해 읽어야할 레코드의 건수가 전체 테이블 레코드의 20~25%를 넘어서면 인덱스 대신 모두 읽어서 필터링합니다.

100만건 중 50만 건을 읽는 경우 50%나 되기 때문에 인덱스 대신 테이블을 처음부터 끝까지 읽어서 처리합니다. 인덱스를 사용하도록 힌트를 추가해도 성능성 얻을 수 있는 이점도 없고 옵티마이저가 기본적으로 무시해서 처리합니다.

어떤 경우에 인덱스를 사용하게 유도할지, 사용하지 못하게 할지 판단하려면 어떻게 인덱스를 이용해서 실제 레코드를 읽는지 알아야 합니다.

프라이머리 키나 유니크 제약 조건을 통해 한 건만 찾아오는 방법입니다.

인덱스 레인지 스캔은 인덱스의 접근 방법 중 대표적인 접근 방식으로 다른 방법보다 빠른 방법입니다. 인덱스를 통해 한 건 이상 읽는 경우 인덱스 레인지 스캔으로 표현할 수 있습니다. 인덱스 레인지 스캔은 검색해야할 인덱스의 범위가 결정됐을 때 사용하는 방식입니다. 검색하려는 수나 검색 결과 레코드 건수와 관계없이 레인지 스캔이라고 합니다. 브랜치 노드에서 리프 노드까지 찾아들어가 시작 지점을 찾고서 순서대로 리프 노드를 읽으면 됩니다. 차례대로 쭉 일갇가 리프 노드 끝가지 읽으면 리프 노드 간의 링크를 이용해 다음 리프 노드를 찾아가 스캔합니다.

실제 데이터를 가져오는 경우도 데이터 정렬 순이 아닌 인덱스 구성 칼럼 순으로 레코드를 가져옵니다. 이 말은 레코드 한건 한건마다 랜덤 I/O가 일어나는 것을 의미하며 인덱스를 통해 읽는 작업이 비용이 많이 드는 이유가 됩니다.

인덱스 레인지 스캔은 다음 단계를 거칩니다.

1. 인덱스에서 조건을 만족하는 값이 저장된 위치를 찾습니다. 이 과정을 인덱스 탐색이라고 합니다.
2. 1번에서 탐색한 위치부터 필요한 만큼 인덱스를 차례대로 쭉 읽습니다. 이 과정을 인덱스 스캔이라고 합니다.
3. 2번에서 읽어 들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 최종 레코드를 읽어옵니다.

쿼리가 필요로 하는 데이터에 따라 3번은 필요하지 않을 수 있는데, 이를 커버링 인덱스라고 합니다. 이 쿼리는 디스크 레코드를 읽지 않아도 되기 때문에 랜덤 읽기가 줄어들어 성능이 빨라집니다.

인덱스를 사용하지만 인덱스의 처음부터 끝까지 모두 읽는 방식으로 조건절에 사용된 칼럼이 인덱스의 첫번째 칼럼이 아닌 경우 사용됩니다. 일반적으로 인덱스의 크기는 테이블 크기보다 작으므로 테이블을 처음부터 끝까지 읽는 것보다 인덱스만 읽는 것이 효율적입니다. 이 방식은 인덱스에 포함된 칼럼으로만으로 처리할 수 있는 경우 효율적이지만, 일반적으로 비효율적이며 인덱스를 생성하는 목적과 다릅니다.

앞선 두 인덱스 스캔은 타이트 인덱스 스캔이라고 할 수 있으며 루스 인덱스 스캔은 느슨하게 인덱스를 읽습니다. 인덱스 레인지 스캔과 비슷하지만 중간에 필요하지 않은 인덱스 키 값은 스킵하고 넘어가는 형태입니다.

인덱스의 핵심은 값이 정렬되어 있는 것이며 인덱스를 구성하는 칼럼의 순서가 매우 중요합니다.

ALTER TABLE employess
    AND INDEX ix_gender_birthdate (gender, birth_date);

이 인덱스를 사용하려면 WHERE 조건 절에 gender 칼럼에 대한 비교 조건이 필수입니다.

SELECT * FROM employees WHERE birth_Date >= '1965-02-01'; 

다읔 뭐리에서는 gender 칼럼에 대한 비교가 없어 위에서 생성한 인덱스를 사용할 수 없었습니다. MySQL 8.0부터는 인덱스 칼럼을 건너 뛰어 사용 가능하게 해주는 인덱스 스킴 스캔이 도입되었습니다. 인덱스 스킵 스캔을 사용하지 않으면 위의 쿼리는 인덱스 풀 스캔을 진행하고 사용하면 레인지 스캔을 사용합니다.

새로 도입된 기능이라 아직 선행 칼럼의 유니크 값 개수가 적어야 하고 쿼리가 인덱스에 존재하는 칼럼으로만 처리가 되야합니다. 유니크 값 개수가 많다면 인덱스 스캔을 시작할 지점을 검색하는 작업이 많이 필요해져 느려질 수 있습니다.

2개 이상의 칼럼이 연결된 인덱스로 레코드의 건수가 적은 경우 브랜치 노드는 없을 수 있습니다. 중요한 점은 칼럼 구성에 따라 정렬되어 있어 첫 번째 컬럼 기준으로 정렬되고 그 다음 컬럼 기준으로 정렬됩니다. 따라서 칼럼 순서가 상당히 중요합니다.

인덱스의 정렬 순서는 오름차순, 내림차순으로 정렬되는데 어디부터 읽을지는 옵티마이저가 실실간으로 만들어냅니다.

다중 칼럼 인덱스 시 칼럼마다 오름차순과 내림차순을 따로 구성할 수 있습니다. 이 때 인덱스를 구성하는 방식에 따라 성능의 차이가 있을 수 있습니다.

• 오름차순 인덱스: 작은 값의 인덱스 키가 B-Tree의 왼쪽으로 정렬
• 내림차순 인덱스: 큰 값의 인덱스 키가 B-Tree의 왼쪽으로 정렬
• 인덱스 정순 스캔: 인덱스 키의 크고 작음과 관계 없이 인덱스 리프 노드 왼쪽부터 오른쪽까지 스캔
• 인덱스 역순 스캔: 인덱스 키의 크고 작음과 관계 없이 인덱스 리프 노드 오른쪽부터 왼쪽까지 스캔

역순 정렬 쿼리가 정순 정렬 쿼리보다 대략 30% 정도 시간이 더 걸립니다.

• 페이지 잠금이 인덱스 정순 스캔에 적합한 구조
• 페이지 내에서 인덱스 레코드가 단방향으로만 연결된 구조

정렬 순서 반대로 읽을 수 있다고 해도 인덱스 정렬 순서와 반대로 읽었을 시 성능이 떨어집니다. 따라서 데이터 조회 쿼리에서 많이 사용하는 정렬 순서대로 인덱스를 생성하는 것이 효율적입니다.

SELECT * FROM dept_emp
WHERE dept_no='d002' AND emp_no >= 10014;

• 케이스 A: INDEX(dept_no, emp_no)
• 케이스 B: INDEX(emp_no, dept_no)

케이스 A의 경우 dept_no=d002 와 emp_no>=10014 를 만족하는 인덱스를 찾고 emp_no 가 d002가 아닐때까지 읽으면 됩니다. 조건 만족 기준부터 조건을 만족하지 않을 때까지 탐색과 정확히 일치합니다. 하지만 B의 경우 emp_no>=10014 인 경우 중에서 dept_no=d002 인지 비교하는 과정이 필요합니다.

B-Tree 인덱스의 특징은 왼쪽 값에 기준해서 오른쪽 값이 정렬되어 있습니다. 이는 한 칼럼 내에서 뿐만 아니라 다중 칼럼에서도 적용됩니다. LIKE를 이용해 뒷부분만 비교하는 경우도 왼쪽이 정해지지 않아 인덱스를 사용할 수 없습니다.

• NOT-EQUAL
• LIKE '%??'
• 스토어드 함수, 다른 연산자로 인덱스 칼럼 변경 후 비교
• NOT-DETERMINISTIC 속성
• 데이터 타입이 서로 다른 비교
• 문자열 데이터 타입의 콜레이션이 다른 경우

전문 검색은 문서 내용 전체를 인덱스화해서 특정 키워드가 포함된 문서를 검색합니다.

• 불용어 처리
• 어근 분석

불용어 처리에서 불필요한 단어들을 필터링하고 어근 분석으로 단어의 뿌리를 찾습니다. 한글이나 일본어는 단어의 변형이 거의 없어 어근 분석보다 형태소 분석이 더 중요한 편입니다. 대표적으로 Mecab 알고리즘이 있습니다.

Mecab은 많은 시간과 노력을 필요로 하는데 단순히 키워드 검색에는 n-gram으로 키워드를 검색해냅니다. n-gram은 몇 글자씩 글자를 잘라서 사용하는 방법입니다.

두 칼럼을 합쳐서 검색해야 하는 경우 실제 칼럼 대신 가상 칼럼을 추가해 인덱스를 생성할 수 있습니다. 함수를 통해 나온 결과 값을 기준으로 인덱스를 만드는 방법도 있습니다.

기본적으로 인덱스는 레코드 1건이 1개의 인덱스를 가집니다. JSON 포맷의 경우 전체 JSON 보다 필드 원소에 대한 인덱스가 필요합니다.

MEMBER OF()
JSON_CONTAINS()
JSON_OVERLAPS()

다음 명령어들로 멀티 밸류 인덱스를 지원합니다.

클러스터링은 여러 개를 하나로 묶는데 MySQL 서버에서는 레코드를 프라이머리 키를 기준으로 묶어서 저장하는 것을 의미합니다. 주로 비슷한 값들은 동시에 조회하는 경우가 많다는 점에 착안합니다. 따라서 프라이머리 키 값에 의해 레코드의 저장 위치가 결정되며 프라이머리 키 값이 변경되면 위치도 변경되는 걸 의미합니다. 프라이머리 키 기반의 검색은 빠르지만 레코드 저장이나 변경은 상대적으로 느려집니다.

1. 프라이머리 키가 있으면 프라이머리를 클러스터링 키로 선택
2. NOT NULL 옵션의 유니크 인덱스 중 첫 번째를 클러스터링 키로 선택
3. 자동으로 유니크한 값을 가지도록 증가되는 칼럼을 내부적으로 추가후, 클러스터링 키로 선택

클러스터링 인덱스는 테이블당 단 하나이므로 가능한 프라이머리 키를 명시적으로 생성하는 것이 좋습니다.

MyISAM이나 MEMORY 테이블처럼 클러스터링 되지 않은 테이블은 처음 젖아된 공간에서 이동하지 않습니다. 프라이머리 키나 세컨드 인덱스는 내부적인 레코드 아이디를 이용하며 둘의 차이점은 없습니다. InnoDB에서도 실제 레코드 주소를 가진다면 클러스터링 키 값이 변경될 때마다 모든 인덱스 주솟값을 변경해야합니다. 이런 오버헤드 제거를 위해 세컨더리 인덱스는 해당 레코드 주소가 아닌 프라이머리 키 값을 저장합니다.

장점은 프라이머리 키로 검색할 때 빠르고, 세컨더리 인덱스가 프라이머리 키를 가져 인덱스만으로 처리되는 경우가 많습니다. 단점은 세컨더리 인덱스가 클러스터링 키 값을 가져 그만큼 인덱스의 크기가 커지고 세컨더리 인덱스로 검색시 프라이머리 키를 거쳐야합니다. INSERT할 때도 프라이머리 키에 의해 레코드 저장 위치가 결정되어 느리고 키를 변경할 때 DELETE와 INSERT 작업이 필요합니다.

• 프라이머리 키는 AUTO-INCREMENT보다 업무적인 칼럼으로 생성
• AUTO-INCREMENT를 사용하더라도 프라이머리 키는 반드시 생성

인덱스보다는 제약조건에 가까우며 테이블에 같은 값이 2개 이상 저장될 수 없습니다.

인덱스 읽기

유니크 인덱스는 1건만 읽으면 되고 세컨더리는 1건 더 읽어 유니크 인덱스가 더 빠르다고 생각할 수 있습니다. 하지만 한 번 더 하는 작업은 디스크 읽기가 아니라 CPU에서 칼럼 값 비교하는 작업이라 성능상 영향이 거의 없습니다. 즉 유니크하지 않은 세컨더리 인덱스는 중복된 값이 허용되어 읽어야 할 레코드가 많아 느린 것이지 인덱스 자체 특성 때문이 아닙니다.

인덱스 쓰기

새로운 값이 INSERT되거나 변경될 때 유니크 인덱스의 경우 중복된 값이 있는지 체크하는 과정이 필요해 세컨더리보다 느립니다.

외래키는 InnoDB 에서만 생성할 수 있으면 외래키 제약이 설정되면 자동으로 연관되는 테이블 칼럼에 인덱스까지 생성됩니다. 외래키가 제거되지 않은 상태에서는 자동으로 생성된 인덱스를 삭제할 수 없습니다.

• 테이블의 변경이 발생하는 경우에만 잠금 경합이 발생합니다.
• 외래 키와 연관되지 않은 칼럼의 변경은 최대한 잠금 경합을 발생시키지 않는다.